第一章：
1.数据库的设计分为三个阶段：
需求分析阶段：分析客户的业务和数据处理需求
概要设计阶段：绘制E-R关系图
详细设计阶段：用数据库的三大范式进行审核，创建数据库和表
2.在E-R关系图中：
矩形表示实体，椭圆表示属性，菱形表示关系，直线用来连接属性和实体
3.数据库的三大范式:
1.第一范式：每列（属性）都是不可再分的最小数据单元，即列不可再分
2.第二范式：确保表中的每列都和主键相关
3.第三范式：不存在传递依赖关系，确保每列都和主键直接相关，而不是间接相关
注意：存在部分依赖不满足第二范式[订单编号，商品编号](组合键)
订单日期与订单编号有关,与商品编号无关
第二章
1.创建一个数据库文件和日子文件
if exists(select * from sysdatabases where name='employees')
drop database employees
create database employees
on primary
(
name='employees_data',
filename='D:\project\employees_data.mdf',
size=10,
filegrowth=10%
)
,
(
次要数据文件
name='employees_data2',
filename='D:\project\employees_data2.ndf',
size=20,
filegrowth=1
)
log on
(
name='employees_log',
filename='D:\project\employees_log.ldf',
size=10,
maxsize=50,
filegrowth=1
)
(
name='employees_log2',
filename='D:\project\employees_log2.ldf',
size=10,
maxsize=50,
filegrowth=1
)
2.删除数据库:
if exists (select * from sysdatabases where name='stuDB')
drop database stuDB
3.创建表：
if exists (select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(
字段1 数据类型 是否为空,
字段2 数据类型 是否为空
)
4.添加约束：
alter table stuInfo
add constraint PK_stuNo primary key (stuNo),
constraint UQ_stuID unique(stuID),
constraint DF_stuAddress default('地址不详') for stuAddress,
constraint CK_stuAge check(stuAge between 15 and 40),
constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
代码创建组合键:
alter table stuInfo
add constraint PK_stuNo primary key (stuNo,stuNo2)
5.删除约束：
alter table 表名
drop constraint 约束名
6.添加一个新的列：
alter table stuInfo
add stuBirthday datetime
7.删除一个列：
alter table stuInfo
drop column stuBirthday
8.创建登录用户：
exec sp_grantlogin 'jbtraining\s26301' --window登录用户
exec sp_addlogin 'zhangsan','1234'
创建数据库用户：
exec sp_grantdbaccess 'jbtraining\s26301','s26301DBUser' --window登录
exec sp_grantdbaccess 'zhangsan','zhangsanDBUser'
给数据库用户授权:
grant create table to s26301DBUser
grant select,insert,update on stuInfo to zhangsanDBuser
第三章
1.变量：@表示局部变量,@@表示全局变量 (一般不声明全局变量)
declare @name varchar(8)
同时声明两个变量:declare @name varchar(9),@seat int
2.set和select赋值语句区别：
set 一次只能给一个变量赋值
select 一次可以给多个变量赋值，select适用于从表中查出数据给变量赋值的情况
注意：select 赋值和查询不能同时进行
3.两种输出语句:
print 局部变量或字符串
select 局部变量 as 自定义列名
注意：print 打印语句要求只能以一种数据类型打印
即print '我是的年龄:'+convert(varchar,@age)才行
4.常用的全局变量:
@@error:最近一条SQL语句是否用错误，如果用错误，将返回非零值
@@identity:可用来查询最后插入的数据的标识值
5.if (条件)
begin
语句
end
else
...
注意：if后面有多条语句的时候，要用begin end
6.while(1=1)
begin
语句
break
end
7.case
when 条件1 then 结果1
when 条件2 then 结果2
else 其他结果
end
注意:可加在SQL语句的任何地方
8.批处理可以提高语句的执行效率,批处理结束的标志是'GO'
9.in 或or 策略性能较低，一般为两个SQL语句的效率更高
select * from student where stuNo=2 or stuNo=3
比select * from student where stuNo=2 union select * from student where stuNo=3
的效率低
第四章
1.建立临时表:
select * into #temp from stuInfo
临时表保存在临时数据库中
临时表不会从一个会话状态转移到另一个会话状态(只能在创建临时表的查询中查询)
当会话状态结束后临时表会自动清除
与存储过程结合使用
2.子查询
select * from stuInfo where stuAge >(select * from stuInfo where stuName='小李')
go
第五章
1.事务：事务及一段SQL代码放到事务中，则这段代码执行时要么失败，要么就成功，失败后，之前执行的代码全部回滚
事务的特性：
原子性：事务的各元素是不可在分的
一致性：事务完成后，数据是一致状态
隔离性：对数据进行修改的所有并发事务是彼此隔离的，一个事务要么在另一个事务之前访问它执行的数据，要么在另一个事务结束后再去访问
持久性：数据的更改是该在硬盘上的
2.事务应用的代码：
例子：张三和李四转账
begin transaction
declare @errorSum int
set @errorSum = 0
update bank set currentMoney = currentMoney-1000 where customerName='张三'
set @erroSum = @errorSum+@@error
update bank set currentMoney = currentMoney+1000 where customerName='李四'
set @erroSum = @errorSum+@@error
if @errorSum > 0
begin
print '事务失败，回滚事务'
rollback transaction
end
else
begin
print '事务成功，提交事务'
commit transaction
end
go
3.索引：缺点：需要更多的硬盘空间，如果插入、更新、删除数据，系统更新索引速度慢
唯一索引：不允许两行具有相同的索引值
主键索引：在创建数据库关系图时，定义为主键列会自动创建主键索引
聚集索引：索引和列值相同，一个表只能有一个，查询速度快
非聚集索引:索引和列值不相同，一个表能有<249个，查询速度慢
创建索引：
if exists(select name from sysindexes where name='IX_stuMarks_writtenExam')
drop index stuMarks.IX_stuMarks_writtenExam
create nonclustered index IX_stuMarks_writtenExam
on stuMarks(writtenExam) ---建议不写with fillfactor=30
4.视图：最大的作用在于：查询，能够实现隐藏一些列的信息，更容易理解和获得数据
创建视图：
if exists (select * from sysobjects where name ='view_stuInfo_stuMarks')
drop view view_stuInfo_stuMarks
go
create view view_stuInfo_stuMarks
as
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo left join stuMarks on stuInfo.stuNo=stuMarks.stuNo
go
select * from view_stuInfo_stuMarks //查询视图中的数据
第六章
1.系统存储过程：
exec sp_databases --列出当前系统中的数据库
exec sp_renamedb 'Northwind','Northwind1' --更改Northwind数据库名为northwind1
use stuDB
go
exec sp_tables --当前数据库中可查询对象的列表
exec sp_columns stuInfo --查看表stuInfo中列的信息
exec sp_help stuInfo --查看表stuInfo的所有信息
exec sp_helpconstraint stuInfo --查看表stuInfo的约束
exec sp_helpindex stuMarks --查看stuMarks的索引
exec sp_helptext 'view_stuInfo_stuMarks' --查看视图的语句文本
exec sp_stored_procedures --返回当前数据库中的存储过程列表
2.用DOS命令创建一个文件夹
exec xp_cmdshell 'mkdir D:\book',no_output
3.创建不带参数的存储过程
if exists(select * from sysobjects where name='pro_stu')
drop procedure pro_stu
go
create procedure pro_stu
as
SQL语句
go
4.创建带输入参数的存储过程
if exists(select * from sysobjects where name='pro_stu')
drop procedure pro_stu
go
create procedure pro_stu
@writtenPass int,
@labPass int
as
SQL语句
go
5.创建带输出参数的存储过程
if exists(select * from sysobjects where name='pro_stu')
drop procedure pro_stu
go
create procedure pro_stu
@writtenPass int output,
@labPass int output
as
SQL语句
go
6.如何接return 值：
if exists(select * from sysobjects where name='pro_stu')
drop procedure pro_stu
go
create procedure pro_stu
@notpassSum int output,
@writtenPass int=60,
@labPass int=60
as
print '笔试及格线：'+convert(varchar,@writtenPass)+'机试及格线：'+convert(varchar,labPass)
print '参加本次考试没有通过的学员：'
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam<@writtenPass or labExam<@labPass
select @notpassSum=count(stuNo) from stuMarks where writtenExam<@writtenPass or labExam<@labPass
return 12
go
declare @sum int
declare @returnValue int
注意：
exec @returnValue = proc_stu @sum output ,64
if @sum>=3
print '未通过人数：'+convert(varchar,@sum)+'人，超过60%，及格分数线还应该下调'
else
print '未通过人数：'+convert(varchar,@sum)+'人，已控制在60%已下，及格分数线适中'
7.raiserror 可以弥补print不足,即可以在程序中显示输出信息
if exists(select * from sysobjects where name='pro_stu')
drop procedure pro_stu
go
create procedure pro_stu
@notpassSum int output,
@writtenPass int=60,
@labPass int=60
as
if(not @writtenPass between 0 and 100) or (not @labPass between 0 and 100)
begin
raiserror(' 及格线错误，请指定0-100之间，统计中断退出')
return
end
print '笔试及格线：'+convert(varchar,@writtenPass)+'机试及格线：'+convert(varchar,labPass)
print '参加本次考试没有通过的学员：'
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam<@writtenPass or labExam<@labPass
select @notpassSum=count(stuNo) from stuMarks where writtenExam<@writtenPass or labExam<@labPass
return 12
go
declare @sum int
declare @return int
注意：
exec @return = proc_stu @sum output ,64
if @sum>=3
print '未通过人数：'+convert(varchar,@sum)+'人，超过60%，及格分数线还应该下调'
else
print '未通过人数：'+convert(varchar,@sum)+'人，已控制在60%已下，及格分数线适中'
8.加密存储过程：
alter procedure 存储过程名
with encryption as SQL内容
9.存储过程注意事项：
(1)不需要有print[打印出的内容不会到程序中显示]
(2)一般和临时表配合使用
(3)执行存储过程就是开启一次会话，执行结束后会话状态结束
(4)和游标配合使用
(5)存储过程没有重载
(6)创建存储过程必须在go的后面，否则报错
(7)存储过程注释：
/*
功能
创建日期
修改日期
修改备注
*/
10.游标:可以把存储过程返回的集合中的数据一行一行
--定义游标
declare mycursor cursor for select * from #tempBook
--定义参数
declare @first_day int
declare @first_pay int
declare @second_pay int
declare @pay_day int
set @total=0
--打开游标
open mycursor
--提取第一条数据
fetch next from mycursor into @first_day,@first_pay,@second_pay,@pay_day
while @@fetch_status = 0
begin
if @pay_day <= @first_day --第一种类型
set @total=@total+@first_pay*@pay_day
else --第二种类型
set @total=@total+@second_pay*(@pay_day-@first_day)+@first_day*@first_pay
fetch next from mycursor into @first_day,@first_pay,@second_pay,@pay_day
end
--关闭游标
close mycursor
--注销游标
deallocate mycursor
GO
declare @tatal int
exec book_proc 'F0612S288',@tatal output
print @tatal
11.触发器：在对表进行插入、更新或删除操作时自动执行的存储过程
(1).它是一种特殊的存储过程
(2).也具备事务的功能
(3).它能在多表之间执行特殊的业务规则
(4).触发器通常用于强制业务规则
(5).触发器是一种高级约束，可以定义比用check约束更为复杂的约束，可执行复杂的SQL语句(if/while/case),可引用其它表中的列
详解：触发器触发时：
系统自动在内存中创建deleted表或inserted表，只读，不允许修改；触发器执行完成后，自动删除
inserted 表 ：
临时保存了插入或更新后的记录行，可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作
deleted 表
临时保存了删除或更新前的记录行，可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作
我的理解：当更改一个表的数据的时候，如果为该表创建了触发器，相关的表的数据会自动的更改
注意：触发器定义在特定的表上，与表相关
自动触发执行
不能直接调用
是一个事务(可回滚)
例子1：insert触发器，在transInfo表上创建触发器，当对transInfo表执行insert语句时自动触发trig_transInfo触发器
create trigger trig_transInfo
on transInfo
for[after] insert
as
declare @type char(4),@outMoney MONEY
declare @myCardID char(10),@balance MONEY
select @type=transType,@outMoney=transMoney，@myCardID=cardID from inserted
if (@type='支取')
update bank set currentMoney=currentMoney-@outMoney where cardID=@myCardID
else
update bank set currentMoney=currentMoney+@outMoney where cardID=@myCardID
go
例子2：delete触发器
create trigger trig_delete_transInfo
on transInfo
for delete
as
print '开始备份数据，请稍后......'
if not exists(select * from sysobjects where name='backupTable')
select * into backupTable from deleted
else
begin
insert into backupTable select * from deleted
print '备份数据成功，备份表中的数据为:'
select * from backupTable
end
go
例子3：update触发器
create trigger trig_update_bank
on bank
for update
as
declare @beforeMoney money,@afterMoney money
select @beforeMoney=currentMoney from deleted
select @afterMoney=currentMoney from inserted
if ABS(@afterMoney-@beforeMoney)>20000
begin
print '交易金额:'+convert(varchar(8), ABS(@afterMoney-@beforeMoney))
raiserror('每笔交易不能超过2万元，交易失败',16,1)
rollback tansaction
end
go
例子4：
问题：交易日期一般由系统自动产生，默认为当前日期。为了安全起见，一般禁止修改，以防舞弊。
update（列名）函数可以检测是否修改了某列
create trigger trig_update_transInfo
on transInfo
for update
as
if update(transDate)
begin
print '交易失败.....'
raiserror(‘安全警告：交易日期不能修改，由系统自动产生',16,1)
rollback transaction
end
go
课外补充：
1.select * from table_name1 where exists(select * from table_name2 where 条件)
2.image 变长二进制数据，最大长度为2^31-1(2G) 可存图片，电影，等等
3.alter table userInfo
add constraint CK_PID check(len(PID)=18 or len(PID)=15),
constraint CK_telephone check(telephone like replicate('[0-9]',4)+'-'+replicate('[0-9]',8)
or telephone like replicate('[0-9]',3)+'-'+replicate('[0-9]',8) or len(telephone)=11)
4. 联合查询
union运算可以把多个查询的结果合并到一个结果集里显示.
union运算的一般语法:[表]查询1 union [ALL]查询2 UNION …
例:返回巴西所有供给商和客户的名字和城市
select companyName,city from suppliers where country = 'Brazil' union select companyName,city from customers where country = 'Brazil'
注意: 缺省的情况下,union子句不返回重复的记录,如果想显示所有记录,可以加ALL选项
union运算要求查询具有(相同数目的字段),但字段数据类型不必相同.
每一个查询参数中可以使用GROUP BY子句或HAVING子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用OREER BY子句.
5.新建默认值
alter table [表名] add constraint 默认值名 default '51WINDOWS.NET' for [字段名]
删除默认值
alter table [表名] drop constraint 默认值名
6. 删除Sql Server 中的日志，减小数据库文件大小
dump transaction 数据库名 with no_log
backup log 数据库名 with no_log
dbcc shrinkdatabase(数据库名)
exec sp_dboption ’数据库名’, ’autoshrink’, ’true’
示例:
dump transaction MyDocument with no_log
backup log MyDocument with no_log
dbcc shrinkdatabase(MyDocument)
exec sp_dboption 'MyDocument','autoshrink','true'
7.返回所有有下过采购单的供应商信息，同时还需要返回供应商最后一次下采购单的时间
select Provider.*,md from Provider
inner join
(
select max(SDate) md,ProID from Stock group by ProID
) tempdt
on tempdt.ProID=Provider.ProID
8.显示今天生日的元老，即生日与今天月日相同
select * from bbsusers where month(ubirthday)=month(getdate()) and day(ubirthday)=day(getdate())
